package com.iking.housekeeping.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;


import com.iking.housekeeping.entity.export.ExportModel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

public class PoiExcelExport {
	// 文件名
	private String fileName ;
	//文件保存路径
	private String fileDir;
	//sheet名
	private String sheetName;
	//表头字体
	private String titleFontType = "Arial Unicode MS";
	//表头背景色
//	private String titleBackColor = "";
	private String titleBackColor = "C1FBEE";
	//表头字号
	private short titleFontSize = 13;
	//添加自动筛选的列 如 A:M
	private String address = "";
	//正文字体
	private String contentFontType = "Arial Unicode MS";
	//正文字号
	private short contentFontSize = 12;
	//Float类型数据小数位
	private String floatDecimal = ".00";
	//Double类型数据小数位
	private String doubleDecimal = ".00";
	//设置列的公式
	private String colFormula[] = null;
	
	DecimalFormat floatDecimalFormat=new DecimalFormat(floatDecimal);
	DecimalFormat doubleDecimalFormat=new DecimalFormat(doubleDecimal);
	
	private HSSFWorkbook workbook = null;
	
	public PoiExcelExport(String fileDir){
	     this.fileDir = fileDir;
	     workbook = new HSSFWorkbook();
	}

    /**
     * 设置表头字体.
     * @param titleFontType
     */
	public void setTitleFontType(String titleFontType) {
		this.titleFontType = titleFontType;
	}
    /**
     * 设置表头背景色.
     * @param titleBackColor 十六进制
     */
	public void setTitleBackColor(String titleBackColor) {
		this.titleBackColor = titleBackColor;
	}
    /**
     * 设置表头字体大小.
     * @param titleFontSize
     */
	public void setTitleFontSize(short titleFontSize) {
		this.titleFontSize = titleFontSize;
	}
    /**
     * 设置表头自动筛选栏位,如A:AC.
     * @param address
     */
	public void setAddress(String address) {
		this.address = address;
	}
    /**
     * 设置正文字体.
     * @param contentFontType
     */
	public void setContentFontType(String contentFontType) {
		this.contentFontType = contentFontType;
	}
    /**
     * 设置正文字号.
     * @param contentFontSize
     */
	public void setContentFontSize(short contentFontSize) {
		this.contentFontSize = contentFontSize;
	}
	/**
	 * 设置float类型数据小数位 默认.00
	 * @param doubleDecimal 如 ".00"
	 */
    public void setDoubleDecimal(String doubleDecimal) {
		this.doubleDecimal = doubleDecimal;
	}
	/**
     * 设置doubel类型数据小数位 默认.00
     * @param floatDecimalFormat 如 ".00
     */
	public void setFloatDecimalFormat(DecimalFormat floatDecimalFormat) {
		this.floatDecimalFormat = floatDecimalFormat;
	}
	/**
	 * 设置列的公式 
	 * @param colFormula  存储i-1列的公式 涉及到的行号使用@替换 如A@+B@
	 */
	public void setColFormula(String[] colFormula) {
		this.colFormula = colFormula;
	}

	public void wirteExcel(List<ExportModel> datas){
		//新建文件
		OutputStream out = null;
		try{
			if(fileDir!=null){
				//有文件路径
				out = new FileOutputStream(fileDir);
			}
			//处理多sheet页
			for (ExportModel data: datas) {
				this.wirte(data);
			}
			workbook.write(out);
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 写excel
	 */
	public void wirte(ExportModel exportModel) {
		//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
		Sheet sheet = workbook.createSheet(exportModel.getSheetName());

		//写入excel的表头
		Row titleNameRow = sheet.createRow(0);
		//设置样式
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, titleFontType, (short) titleFontSize);
		titleStyle = (HSSFCellStyle) setColor(titleStyle, titleBackColor, (short) 10);
		//设置表头
		for (int i = 0; i < exportModel.getColumnName().length; i++) {
			//设置宽度
			sheet.setColumnWidth(i, 25 * 256);
			Cell cell = titleNameRow.createCell(i);
			cell.setCellStyle(titleStyle);
			cell.setCellValue(exportModel.getColumnName()[i].toString());
		}
		int rowIndex=1;
		for(Map<String,Object> rowData : exportModel.getDataList()){
			int columnIndex =0;
			Row dataRow = sheet.createRow(rowIndex);
			//根据预先配置的属性，从集合中获取数据写入Excel中
			for(String property : exportModel.getPropertyName()){
				Cell cell = dataRow.createCell(columnIndex);
				Object ob =rowData.get(property);
				if(ob==null){
					cell.setCellValue("");
				}else{
					cell.setCellValue(ob.toString());
				}
//				System.out.println(ob);
				columnIndex++;
			}
			rowIndex++;
		}
	}




	public void wirteExcels(List<ExportModel> datas){
		//新建文件
		OutputStream out = null;
		try{
			if(fileDir!=null){
				//有文件路径
				out = new FileOutputStream(fileDir);
			}
			//处理多sheet页
			for (ExportModel data: datas) {
				this.wirtes(data);
			}
			workbook.write(out);
		}catch (Exception e){
			e.printStackTrace();
		}finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 多行表头
	 * dataList：导出的数据；
	 * sheetName：表头名称；
	 * head0：表头第一行列名；
	 * head1：表头第二行列名；
	 *
	 */
	public void wirtes(ExportModel exportModel) {
		//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
		Sheet sheet = workbook.createSheet(exportModel.getSheetName());
		//写入excel的表头
		Row titleNameRow = sheet.createRow(0);
		//设置样式
		CellStyle titleStyle = workbook.createCellStyle();
		HSSFFont hssfFont = workbook.createFont();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 10);
		Row  rows = sheet.createRow(3);
		Object head=exportModel.getColumnNames()[2];
		String [] head0 =(String [])head;
		Object heads=exportModel.getColumnNames()[3];
		String [] head1 =(String [])heads;
		//设置表头
		for (int i = 0; i < 3; i++) {
			Row  row = sheet.createRow(i);
			if(i <=1){
				// 第一行和第二行表头标题
				if(exportModel.getCount()>0){
					sheet.addMergedRegion(new CellRangeAddress(i, i, 0, exportModel.getCount()));
				}else{
					sheet.addMergedRegion(new CellRangeAddress(i, i, 0, 10));
				}

				row.setHeight((short) 0x249);
				Cell cell = row.createCell(0);
				cell.setCellValue(exportModel.getColumnNames()[i].toString());
				if(i==0){
				CellStyle titleStyless = workbook.createCellStyle();
				titleStyless.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
				HSSFFont fonts = workbook.createFont();
				fonts.setFontHeightInPoints((short)18);
				titleStyless.setFont(fonts);
				cell.setCellStyle(titleStyless);}
				if(i==1){
					CellStyle titleStyles = workbook.createCellStyle();
					titleStyles.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居左
					HSSFFont fontss = workbook.createFont();
					fontss.setColor(HSSFColor.RED.index);
					fontss.setFontHeightInPoints((short)13);
					titleStyles.setFont(fontss);
					cell.setCellStyle(titleStyles);
				}

			}else if(i == 2){
				int count=11;
		       for (int  j = 0; j < head0.length; j++) {
			   if(j < 11){
			     sheet.addMergedRegion(new CellRangeAddress(2, 3, j, j));
			     Cell cell =row.createCell(j);
			     cell.setCellValue(head0[j]);
			     cell.setCellStyle(titleStyle);
			   }else{
				   sheet.addMergedRegion(new CellRangeAddress(i, i, count, count+2));
				   Cell cell =row.createCell(count);
			       cell.setCellValue(head0[j]);
				    cell.setCellStyle(titleStyle);
				   for(int t =0;t< head1.length ;t++){
				  	Cell cells =rows.createCell(count);
				  	cells.setCellValue(head1[t]);
				    cells.setCellStyle(titleStyle);
					   count++;
				    }
				}
			}
		 }
		}
		int rowIndex=4;
		for(Map<String,Object> rowData : exportModel.getDataList()){
			int columnIndex =0;
			Row dataRow = sheet.createRow(rowIndex);
			//根据预先配置的属性，从集合中获取数据写入Excel中
			for(String property : exportModel.getPropertyName()){
				Cell cell = dataRow.createCell(columnIndex);
				Object ob =rowData.get(property);
				if(ob==null||"".equals(ob) ){
					cell.setCellValue("");
				}
				else{
					cell.setCellValue(ob.toString());
				}
//				System.out.println(ob);
				columnIndex++;
			}
			rowIndex++;
		}
	}
    /**
     * 将16进制的颜色代码写入样式中来设置颜色
     * @param style  保证style统一
     * @param color 颜色：66FFDD
     * @param index 索引 8-64 使用时不可重复
     * @return
     */
    public CellStyle setColor(CellStyle style,String color,short index){
    	if(color != "" && color != null){
			//转为RGB码
    		int r = Integer.parseInt((color.substring(0,2)),16);   //转为16进制
    		int g = Integer.parseInt((color.substring(2,4)),16);
    		int b = Integer.parseInt((color.substring(4,6)),16);
    		//自定义cell颜色
    		HSSFPalette palette = workbook.getCustomPalette();
    		palette.setColorAtIndex((short)index, (byte) r, (byte) g, (byte) b);
    		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    		style.setFillForegroundColor(index);
		}
        return style;
    }

    /**
     * 设置字体并加外边框
     * @param style  样式
     * @param fontName  字体名
     * @param size  大小
     * @return CellStyle
     */
    public CellStyle setFontAndBorder(CellStyle style,String fontName,short size){
    	HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(size);
        font.setFontName(fontName);
        Short s=1;
        font.setBoldweight(s);
        style.setFont(font);
        style.setBorderBottom(CellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        style.setBorderTop(CellStyle.BORDER_THIN);//上边框
        style.setBorderRight(CellStyle.BORDER_THIN);//右边框
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        return style;
    }
	/**
	 * 删除文件
	 * @return boolean
	 */
    public boolean deleteExcel(){
    	boolean flag = false;
    	File file = new File(this.fileDir);
    	// 判断目录或文件是否存在
        if (!file.exists()) {  // 不存在返回 false
            return flag;
        } else {
            // 判断是否为文件
            if (file.isFile()) {
            	// 为文件时调用删除文件方法
                file.delete();
                flag = true;
            }
        }
        return flag;
    }
    /**
	 * 删除文件
	 * @return boolean
	 */
    public boolean deleteExcel(String path){
    	boolean flag = false;
    	File file = new File(path);
    	// 判断目录或文件是否存在  
        if (!file.exists()) {  // 不存在返回 false  
            return flag;  
        } else {  
            // 判断是否为文件  
            if (file.isFile()) {  // 为文件时调用删除文件方法  
                file.delete();
                flag = true;
            } 
        }
        return flag;
    }
}
